import sys
sys.path.append('../kernel')
from model import Model
class CityModel(Model):
    def __init__(self):
        # 链接数据库
        super().__init__()
    # 判断数据是否存在
    def isCity_exists(self,city_name):
        # 执行查询
        sql = "select id,`name` from city WHERE `name`='%s'" % city_name
        # sql = "select id,`name` from city"
        self.cursor.execute(sql)
        if(self.cursor.rowcount>=1):
            return 1
        else:
            return 0
    #添加城市
    def add_city(self,id,name,province,superior,latitude,longitude):
        print(name,province,superior,id,latitude,longitude)
        sql = "INSERT INTO city SET `name`='%s',province='%s',superior='%s',hf_id='%s',latitude='%f',longitude='%f'" % (name,province,superior,id,latitude,longitude)
        self.cursor.execute(sql)
        self.conn.commit()#提交
    #添加景点
    def add_scenic(self,name,city_name):
        sql = "select id,`name` from scenic WHERE `name`='%s'" % name
        # sql = "select id,`name` from city"
        self.cursor.execute(sql)
        if (self.cursor.rowcount >= 1):
            return 1
        else:
            #获取城市的id
            city_sql="select id,`name` from city WHERE `name`='%s'" % city_name
            self.cursor.execute(city_sql)
            city_data=self.cursor.fetchone()
            print(city_data)
            exit()
            if(city_data==None):
                print('城市不存在：%s' % city_name)
            else:
                # 查询出城市的id并和景点一起写入数据库
                sql = "INSERT INTO scenic SET `name`='%s',city_id='%d'" % (name,city_data['id'])
                self.cursor.execute(sql)
                self.conn.commit()#提交
    def add_weather_code(self,code,name,icon):
        sql = "INSERT INTO weather_code SET code='%d',`name`='%s',icon='%s'" % (code,name,icon)
        self.cursor.execute(sql)
        self.conn.commit()  # 提交
    def get_city_by_id(self,id):
        sql = "select id,`name`,hf_id from city WHERE `id`>'%d' limit 150 " % id
        # sql = "select id,`name` from city"
        self.cursor.execute(sql)
        city_data = self.cursor.fetchall()
        return city_data

    #添加城市酒店url
    def add_city_hotel_url(self,url,id):
        sql = "UPDATE city SET `xc_hotel_url`='%s' WHERE id='%d'" % (url,id)
        self.cursor.execute(sql)
        self.conn.commit()  # 提交
    #根据城市名称获取城市id
    def get_city_id_by_name(self,name):
        sql="SELECT id FROM city WHERE name like '%s%%'" % name
        self.cursor.execute(sql)
        city_data = self.cursor.fetchall()
        return city_data
    def __del__(self):
        #计数数据库链接
        self.cursor.close()
        self.conn.close()